from django.db import models

# Create your models here.
import sqlite3
from gs_scheduler.management.commands.config import sqllite_path
from datetime import datetime,timedelta
from pytz import timezone,utc
from apscheduler.jobstores.sqlalchemy import SQLAlchemyJobStore

import sqlite3

#时间戳转成时间字符串
def timestamp_to_timestr(timestamp):
    '''
    :param timestamp:  时间戳
    :return: 时间字符串
    '''
    # 将时间戳转换为日期时间对象
    dt_object = datetime.fromtimestamp(timestamp)
    # 将日期时间对象格式化为字符串
    date_string = dt_object.strftime('%Y-%m-%d %H:%M:%S')
    return date_string

class SQLiteDB:
    def __init__(self):
        self.conn = sqlite3.connect(sqllite_path)
    #创建记录定时任务运行的历史表（调度器使用）
    def create_apscheduler_history(self):
        query = '''
        CREATE TABLE apscheduler_history (
            id INTEGER PRIMARY KEY,
            job_id varchar,
            run_time TEXT,
            is_error TINYINT,
            error_msg TEXT NULL)'''
        # 创建一个游标对象
        cur = self.conn.cursor()
        # 先判断表是否存在
        cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?", ('apscheduler_history',))
        result = cur.fetchone()
        if not result:
            # 执行创建表操作
            cur.execute(query)
        cur.close()

    #将任务运行的结果记录到数据库中（调度器使用）
    def insert_into_apscheduler_history(self,data_list:list):
        if len(data_list)==4:#异常执行的任务
            sql = """
            INSERT INTO apscheduler_history (job_id,run_time,is_error,error_msg) VALUES (?,?,?,?)
            """
        else:#正常执行的任务
            sql = """
            INSERT INTO apscheduler_history (job_id,run_time,is_error) VALUES (?,?,?)
            """
        cur = self.conn.cursor()
        cur.execute(sql, data_list)
        self.conn.commit()
        cur.close()

    # 删除8小时前的历史记录（调度器使用）
    def delete_8hour_before_history(self):
        before_8hour = (datetime.now() - timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S')

        sql = '''
        DELETE FROM apscheduler_history WHERE run_time < ?
        '''
        cur = self.conn.cursor()
        cur.execute(sql, (before_8hour,))
        self.conn.commit()
        cur.close()

    #查询每个任务的下次运行时间（api展示）
    def fetch_all_next_run_time(self):
        sql = 'SELECT id,next_run_time FROM apscheduler_jobs'
        #获取游标
        cur = self.conn.cursor()
        #执行sql语句
        cur.execute(sql)
        # 获取查询结果
        rows = cur.fetchall()
        data_list = []
        for row in rows:
            id = row[0]
            next_run_time = timestamp_to_timestr(row[1])
            data_list.append({'id': id, 'next_run_time': next_run_time})
        cur.close()
        return data_list

    # 查询所有任务最近10次运行记录（api展示）
    def fetch_all_run_history(self):
        '''
        :param query:'SELECT id,next_run_time,job_state FROM apscheduler_jobs'
        :return:
        '''
        data_list = self.fetch_all_next_run_time()
        id_list = (dic.get('id') for dic in data_list)
        # 创建一个游标对象
        cur = self.conn.cursor()
        sql = """
           SELECT id,job_id,run_time FROM apscheduler_history WHERE job_id = ? ORDER BY id DESC LIMIT 10
           """
        ret_list = []
        for id in id_list:
            # 执行查询
            cur.execute(sql, (id,))
            # 获取查询结果
            rows = cur.fetchall() or []
            dic = {'id': id, 'last_run_time': [], 'msg': '最近10次运行时间'}
            for row in rows:
                run_time = row[2]
                dic['last_run_time'].append(run_time)
            ret_list.append(dic)

        cur.close()
        return ret_list

    #获取任务最近运行失败情况(api展示)
    def fetch_all_error_history(self):
        data_list = self.fetch_all_next_run_time()
        id_list = (dic.get('id') for dic in data_list)
        # 创建一个游标对象
        cur = self.conn.cursor()
        sql = """
        SELECT run_time,error_msg FROM apscheduler_history WHERE is_error=1 AND job_id = ? ORDER BY id DESC LIMIT 5
        """
        ret_list = []
        for id in id_list:
            # 执行查询
            cur.execute(sql, (id,))
            # 获取查询结果
            rows = cur.fetchall() or []
            dic = {'id': id, 'last_run_time': [], 'msg': '最近5次执行失败'}
            for row in rows:
                run_time = row[0]
                error_msg = row[1]
                dic['last_run_time'].append({'run_time':run_time,'error':error_msg})
            ret_list.append(dic)

        cur.close()
        return ret_list


    def close(self):
        if self.conn:
            self.conn.close()








